package com.munichosica.myapp.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.munichosica.myapp.dao.ProveedorDao;
import com.munichosica.myapp.dto.Almacen;
import com.munichosica.myapp.dto.Proveedor;
import com.munichosica.myapp.dto.Tienda;
import com.munichosica.myapp.exceptions.AlmacenDaoException;
import com.munichosica.myapp.exceptions.ProveedorDaoException;
import com.munichosica.myapp.exceptions.TiendaDaoException;

public class ProveedorDaoImpl implements ProveedorDao{
	private StringBuilder sql;
	private Logger logger=Logger.getLogger(ProveedorDaoImpl.class);

	public ProveedorDaoImpl() {
		sql=new StringBuilder();
	}
	
	@Override
	public List<Proveedor> listarPorCriterio(String criterio, String texto,
			String estado) throws ProveedorDaoException {
		sql.append("SELECT procodigo_i AS CODIGO,"
				+ "prorazonsocial_v AS RAZONSOCIAL,"
				+ "proruc_v AS RUC,"
				+ "prodireccion_v AS DIRECCION,"
				+ "protelefono_v AS TELEFONO,"
				+ "pronombre_v AS NOMBRE,"
				+ "propaterno_v AS PATERNO,"
				+ "promaterno_v AS MATERNO,"
				+ "prodni_v AS DNI,"
				+ "proestado_c AS ESTADO "
				+ "FROM PROVEEDOR "
				+ "WHERE PROESTADO_C LIKE '%"+estado.trim()+"%' ");
		if(!criterio.isEmpty()) sql.append("AND "+criterio.trim()+" LIKE '%"+texto.trim()+"%'");
		
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		List<Proveedor> lista=null;
		try {
			conn=ResourceManager.getConnection();
			stmt=conn.prepareStatement(sql.toString());
			boolean results=stmt.execute();
			if(results){
				lista=new ArrayList<Proveedor>();
				rs=stmt.getResultSet();
				Proveedor proveedor=null;
				while(rs.next()){
					proveedor=new Proveedor();
					proveedor.setProcodigoI(rs.getInt("CODIGO"));
					proveedor.setProrazonsocialV(rs.getString("RAZONSOCIAL"));
					proveedor.setProrucV(rs.getString("RUC"));
					proveedor.setProdireccionV(rs.getString("DIRECCION"));
					proveedor.setProtelefonoV(rs.getString("TELEFONO"));
					proveedor.setPronombreV(rs.getString("NOMBRE"));
					proveedor.setPropaternoV(rs.getString("PATERNO"));
					proveedor.setPromaternoV(rs.getString("MATERNO"));
					proveedor.setProdniV(rs.getString("DNI"));
					proveedor.setProestadoV(rs.getString("ESTADO"));
					lista.add(proveedor);
				}
			}
		} catch (SQLException e) {
			throw new ProveedorDaoException(e.getMessage(), e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			ResourceManager.close(conn);
		}
		return lista;
	}

	@Override
	public void insertar(Proveedor proveedor) throws ProveedorDaoException {
		sql.append("INSERT INTO proveedor "
				+ "(prorazonsocial_v, "
				+ "proruc_v, "
				+ "prodireccion_v, "
				+ "protelefono_v,"
				+ "pronombre_v,"
				+ "propaterno_v,"
				+ "promaterno_v,"
				+ "prodni_v, "
				+ "prousuarioregistra_v,"
				+ "profecharegistra_f,"
				+ "proip_v,"
				+ "proestado_c) "
				+ "VALUES "
				+ "(?,?,?,?,?,?,?,?,?,CURDATE(),?,?)");
		PreparedStatement stmt=null;
		Connection conn=null;
		ResultSet rs=null;
		try {
			conn=ResourceManager.getConnection();
			stmt=conn.prepareStatement(sql.toString(),PreparedStatement.RETURN_GENERATED_KEYS);
			stmt.setString(1, proveedor.getProrazonsocialV());
			stmt.setString(2, proveedor.getProrucV());
			stmt.setString(3, proveedor.getProdireccionV());
			stmt.setString(4, proveedor.getProtelefonoV());
			stmt.setString(5, proveedor.getPronombreV());
			stmt.setString(6, proveedor.getPropaternoV());
			stmt.setString(7, proveedor.getPromaternoV());
			stmt.setString(8, proveedor.getProdniV());
			stmt.setString(9, proveedor.getProusuarioregistraV());
			stmt.setString(10, proveedor.getProipV());
			stmt.setString(11, proveedor.getProestadoV());
			int results=stmt.executeUpdate();
			if(results>0){
				rs=stmt.getGeneratedKeys();
				if(rs.next()){
					proveedor.setProcodigoI(rs.getInt(1));
				}
			}
		}catch (SQLException e) {
			throw new ProveedorDaoException(e.getMessage(),e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			ResourceManager.close(conn);
		}
	}

	@Override
	public void modificar(Proveedor proveedor) throws ProveedorDaoException {
		sql.append("UPDATE proveedor "
				+ "SET "
				+ "prorazonsocial_v = ?,"
				+ "proruc_v = ?,"
				+ "prodireccion_v = ?,"
				+ "protelefono_v = ?,"
				+ "pronombre_v = ?,"
				+ "propaterno_v = ?,"
				+ "promaterno_v = ?,"
				+ "prodni_v = ?, "
				+ "prousuarioactualiza_v = ?,"
				+ "profechaactualiza_f = CURDATE(),"
				+ "proip_v = ?,"
				+ "proestado_c = ?"
				+ "WHERE procodigo_i = ?");
		PreparedStatement stmt=null;
		Connection conn=null;
		ResultSet rs=null;
		try {
			conn=ResourceManager.getConnection();
			stmt=conn.prepareStatement(sql.toString(),PreparedStatement.RETURN_GENERATED_KEYS);
			stmt.setString(1, proveedor.getProrazonsocialV());
			stmt.setString(2, proveedor.getProrucV());
			stmt.setString(3, proveedor.getProdireccionV());
			stmt.setString(4, proveedor.getProtelefonoV());
			stmt.setString(5, proveedor.getPronombreV());
			stmt.setString(6, proveedor.getPropaternoV());
			stmt.setString(7, proveedor.getPromaternoV());
			stmt.setString(8, proveedor.getProdniV());
			stmt.setString(9, proveedor.getProusuarioactualizaV());
			stmt.setString(10, proveedor.getProipV());
			stmt.setString(11, proveedor.getProestadoV());
			stmt.setInt(12, proveedor.getProcodigoI());
			stmt.executeUpdate();
		}catch (SQLException e) {
			throw new ProveedorDaoException(e.getMessage(),e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			ResourceManager.close(conn);
		}
	}

	@Override
	public Proveedor obtener(Integer codigo) throws ProveedorDaoException {
		sql.append("SELECT procodigo_i AS CODIGO,"
				+ "prorazonsocial_v AS RAZONSOCIAL,"
				+ "proruc_v AS RUC,"
				+ "prodireccion_v AS DIRECCION,"
				+ "protelefono_v AS TELEFONO,"
				+ "pronombre_v AS NOMBRE,"
				+ "propaterno_v AS PATERNO,"
				+ "promaterno_v AS MATERNO,"
				+ "prodni_v AS DNI,"
				+ "proestado_c AS ESTADO "
				+ "FROM PROVEEDOR "
				+ "WHERE procodigo_i = ? ");
		
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		Proveedor proveedor=null;
		try {
			conn=ResourceManager.getConnection();
			stmt=conn.prepareStatement(sql.toString());
			stmt.setInt(1, codigo);
			boolean results=stmt.execute();
			if(results){
				rs=stmt.getResultSet();
				while(rs.next()){
					proveedor=new Proveedor();
					proveedor.setProcodigoI(rs.getInt("CODIGO"));
					proveedor.setProrazonsocialV(rs.getString("RAZONSOCIAL"));
					proveedor.setProrucV(rs.getString("RUC"));
					proveedor.setProdireccionV(rs.getString("DIRECCION"));
					proveedor.setProtelefonoV(rs.getString("TELEFONO"));
					proveedor.setPronombreV(rs.getString("NOMBRE"));
					proveedor.setPropaternoV(rs.getString("PATERNO"));
					proveedor.setPromaternoV(rs.getString("MATERNO"));
					proveedor.setProdniV(rs.getString("DNI"));
					proveedor.setProestadoV(rs.getString("ESTADO"));
				}
			}
		} catch (SQLException e) {
			throw new ProveedorDaoException(e.getMessage(), e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			ResourceManager.close(conn);
		}
		return proveedor;
	}

	@Override
	public List<Proveedor> listarTodos() throws ProveedorDaoException {
		sql.append("SELECT procodigo_i AS CODIGO, " +
				"prorazonsocial_v AS RAZON_SOCIAL, " +
				"proruc_v AS RUC, " +
				"prodireccion_v AS DIRECCION, " +
				"protelefono_v AS TELEFONO  " +
				"FROM PROVEEDOR " +
				"WHERE proestado_c != 'I' " +
				"ORDER BY procodigo_i ASC");
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		List<Proveedor> lista=null;
		try {
			conn=ResourceManager.getConnection();
			stmt=conn.prepareStatement(sql.toString());
			boolean results=stmt.execute();
			if(results){
				lista=new ArrayList<Proveedor>();
				rs=stmt.getResultSet();
				Proveedor proveedor=null;
				while(rs.next()){
					proveedor=new Proveedor();
					proveedor.setProcodigoI(rs.getInt("CODIGO"));
					proveedor.setProrazonsocialV(rs.getString("RAZON_SOCIAL"));
					proveedor.setProrucV(rs.getString("RUC"));
					proveedor.setProdireccionV(rs.getString("DIRECCION"));
					proveedor.setProtelefonoV(rs.getString("TELEFONO"));
					lista.add(proveedor);
				}
			}
		} catch (SQLException e) {
			throw new ProveedorDaoException(e.getMessage(),e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			ResourceManager.close(conn);
		}
		
		return lista;
	}

	@Override
	public void eliminar(Proveedor proveedor) throws ProveedorDaoException {
		sql.append("UPDATE proveedor "
				+ "SET "
				+ "prousuarioactualiza_v = ?,"
				+ "profechaactualiza_f = CURDATE(),"
				+ "proip_v = ?,"
				+ "proestado_c = 'I' "
				+ "WHERE procodigo_i = ?");
		PreparedStatement stmt=null;
		Connection conn=null;
		ResultSet rs=null;
		try {
			conn=ResourceManager.getConnection();
			stmt=conn.prepareStatement(sql.toString(),PreparedStatement.RETURN_GENERATED_KEYS);
			stmt.setString(1, proveedor.getProusuarioactualizaV());
			stmt.setString(2, proveedor.getProipV());
			stmt.setInt(3, proveedor.getProcodigoI());
			stmt.executeUpdate();
		}catch (SQLException e) {
			throw new ProveedorDaoException(e.getMessage(),e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			ResourceManager.close(conn);
		}
	}
}
